{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python生成Excel文件的三种方法"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. 类库xlwt/xlrd\n",
    "  - 用于生成和读取比较老的excel文件，比如xls格式，最大行数限制为65536行\n",
    "  - 文档地址：https://xlwt.readthedocs.io/en/latest/\n",
    "2. 类库openpyxl\n",
    "  - 用于生成2010之后新的excel文件，比如xlsx格式\n",
    "  - 文档地址：https://openpyxl.readthedocs.io/en/stable/\n",
    "3. 类库pandas\n",
    "  - pandas是最强大的数据分析库，自带excel读取和生成模块\n",
    "  - 文档地址：https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 0、读取txt文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = []\n",
    "datas = []\n",
    "with open(\"./pvuv.txt\") as fin:\n",
    "    is_first_line = True\n",
    "    for line in fin:\n",
    "        line = line[:-1]\n",
    "        if is_first_line:\n",
    "            is_first_line = False\n",
    "            columns = line.split(\"\\t\")\n",
    "            continue\n",
    "        \n",
    "        datas.append(line.split(\"\\t\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['日期', 'PV', 'UV']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['2019-07-15', '15000', '150'],\n",
       " ['2019-07-14', '14000', '140'],\n",
       " ['2019-07-13', '13000', '130'],\n",
       " ['2019-07-12', '12000', '120'],\n",
       " ['2019-07-11', '11000', '110'],\n",
       " ['2019-07-10', '10000', '100'],\n",
       " ['2019-07-9', '9000', '90'],\n",
       " ['2019-07-8', '8000', '80'],\n",
       " ['2019-07-7', '7000', '70'],\n",
       " ['2019-07-6', '6000', '60'],\n",
       " ['2019-07-5', '5000', '50'],\n",
       " ['2019-07-4', '4000', '40'],\n",
       " ['2019-07-3', '3000', '30'],\n",
       " ['2019-07-2', '2000', '20'],\n",
       " ['2019-07-1', '1000', '10']]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1、使用xlwt生成xls的excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install xlwt\n",
    "import xlwt\n",
    "workbook = xlwt.Workbook(encoding='utf-8')\n",
    "sheet = workbook.add_sheet('pvuv_sheet')\n",
    "\n",
    "# 写入标题\n",
    "for col,column in enumerate(columns):\n",
    "    sheet.write(0, col, column)\n",
    "# 写入每一行\n",
    "for row, data in enumerate(datas):\n",
    "    for col, column_data in enumerate(data):\n",
    "        sheet.write(row+1, col, column_data)\n",
    "\n",
    "workbook.save('./output/pvuv_xlwt.xls')   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2、使用openpyxl生成xlsx的excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install openpyxl\n",
    "from openpyxl import Workbook\n",
    "workbook = Workbook()\n",
    "\n",
    "# 默认sheet\n",
    "sheet = workbook.active\n",
    "sheet.title = \"默认sheet\"\n",
    "# sheet = workbook.create_sheet(title=\"新sheet\")\n",
    "sheet.append(columns)\n",
    "for data in datas:\n",
    "    sheet.append(data)\n",
    "\n",
    "workbook.save('./output/pvuv_openpyxl.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3、使用pandas生成excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install pandas\n",
    "import pandas as pd\n",
    "\n",
    "# 读取文本文件\n",
    "data = pd.read_csv(\"./pvuv.txt\", sep=\"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>PV</th>\n",
       "      <th>UV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2019-07-15</td>\n",
       "      <td>15000</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-14</td>\n",
       "      <td>14000</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2019-07-13</td>\n",
       "      <td>13000</td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2019-07-12</td>\n",
       "      <td>12000</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-11</td>\n",
       "      <td>11000</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           日期     PV   UV\n",
       "0  2019-07-15  15000  150\n",
       "1  2019-07-14  14000  140\n",
       "2  2019-07-13  13000  130\n",
       "3  2019-07-12  12000  120\n",
       "4  2019-07-11  11000  110"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_excel(\"./output/pvuv_pandas.xls\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
